<?php
require_once('./define/define_table.php');
class Statistique{

 
    public function getAverageNoteForEvenement($id_evenement){
        global $db;
        try{
            $sql = "SELECT AVG(note) AS avg_note FROM ".NOTE_TABLE."
            		WHERE id_evenement = :id_evenement";
            $sth = $db->prepare($sql);
            $sth->bindValue(":id_evenement", $id_evenement, PDO::PARAM_INT);
            $sth->execute();
            return $sth->fetch(PDO::FETCH_OBJ);

        }catch(PDOException $e){
            echo $e.getMessage();
        }
    }

    public function getTopMaxParticipation(){
        global $db;
        try{
            $sql = "SELECT id_individu, nom, prenom, COUNT(id_evenement) AS nb_participation FROM ".PARTICIPANT_TABLE."
					JOIN ".INDIVIDU_TABLE." USING(id_individu)
					GROUP BY id_individu
					ORDER BY COUNT(id_evenement) DESC";
            $sth = $db->prepare($sql);
            $sth->execute();
            PrintQuery::queryPrint($sth);
            return $sth->fetchAll(PDO::FETCH_OBJ);

        }catch(PDOException $e){
            echo $e.getMessage();
        }
    }

    public function getTopEvenementByNote(){
        global $db;
        try{
            $sql = "SELECT AVG( note ) AS avg_note, nom FROM ".NOTE_TABLE."
        		JOIN ".EVENEMENT_TABLE." USING ( id_evenement )
        		GROUP BY id_evenement
        		ORDER BY AVG( note ) DESC";
            $sth = $db->prepare($sql);
            $sth->execute();
            PrintQuery::queryPrint($sth);
            return $sth->fetchAll(PDO::FETCH_OBJ);
        }catch(PDOException $e){
            echo $e.getMessage();
        }
    }

    public function getAverageCommentPerNews($id_individu){
        global $db;
        try{
            $sql = "SELECT COUNT(id_commentaire) / (SELECT COUNT(id_news) FROM ".NEWS_TABLE.") FROM  ".COMMENT_NEWS_TABLE."
            		WHERE id_individu = :id_individu";
            $sth = $db->prepare($sql);
            $sth->bindValue(":id_individu", $id_individu, PDO::PARAM_INT);
            $sth->execute();
            $nb = $sth->fetch();
            return $nb[0];
        }catch(PDOException $e){
            $e->getMessage();
        }
    }

    public function getFinance($id_association, $date){
        global $db;
        try{
            $sql_montant = "SELECT getfinance(:id_association, :datef)";
            $sth = $db->prepare($sql_montant);
            $sth->bindValue(":id_association", $id_association, PDO::PARAM_INT);
            $sth->bindValue(":datef", $date , PDO::PARAM_STR);
            $sth->execute();
            $fin = $sth->fetch();
            return $fin[0];

        }catch(PDOException $e){
            $e->getMessage();
        }
    }
    
    public function getTopFinance($date){
        global $db;
        try{
            $sql = "SELECT id_association, getfinance(id_association, :date) AS finance FROM ".ASSOCIATION_TABLE."
					ORDER BY finance DESC";
            $sth = $db->prepare($sql);
            $sth->bindValue(":date", $date , PDO::PARAM_STR);
            $sth->execute();
            return $sth->fetchAll(PDO::FETCH_CLASS, "Statistique");
        }catch(PDOException $e){
            echo $e->getMessage();
        }
    }

}
?>
